﻿using System;
using System.IO;
using GemBox.Spreadsheet;

namespace Dala.Samples.Insert_Into_Database_from_Excel
{
    public partial class Insert_Into_Database_from_Excel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

            }
        }
        protected void btnImport_Click(object sender, EventArgs e)
        {
            try
            {
                int successCount = 0, totalCount = 0;
                string physicalTempFilePath = ViewState["physicalTempFilePath"].ToString();
                ExcelFile xclFile = LoadExcelFile(physicalTempFilePath);
                ImportData(xclFile, "Sheet1", out successCount, out totalCount);
                // You need to put  
                xclFile.ClosePreservedXlsx();
                DisplaySuccessMessage();
            }
            catch (Exception)
            {
                DisplayErrorMessage();
            }
        }
        private ExcelFile LoadExcelFile(string physicalFilePath)
        {
            ExcelFile xclFile = new ExcelFile();
            FileInfo xclFileInfo = new FileInfo(physicalFilePath);
            FileStream dataFile = new FileStream(physicalFilePath, FileMode.Open);
            switch (xclFileInfo.Extension.ToLower())
            {
                case ".xls":
                    xclFile.LoadXls(dataFile);
                    break;
                case ".xlsx":
                    xclFile.LoadXlsx(dataFile, XlsxOptions.PreserveKeepOpen);
                    break;
            }
            dataFile.Close();
            return xclFile;
        }
        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (fluInput.HasFile)
            {
                string contentType = fluInput.PostedFile.ContentType.ToLower();
                string tempPath = "~/upload-files/Excel/" + fluInput.FileName;
                if (contentType == "application/vnd.ms-excel"
                    || contentType == "application/x-zip-compressed"
                    || contentType == "application/octet-stream"
                    || contentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                {
                    string physicalTempFilePath = Server.MapPath(tempPath);
                    if (File.Exists(physicalTempFilePath))
                    {
                        File.Delete(physicalTempFilePath);
                    }
                    fluInput.SaveAs(physicalTempFilePath);
                    try
                    {
                        ExcelFile xclFile = LoadExcelFile(physicalTempFilePath);
                        ViewState["physicalTempFilePath"] = physicalTempFilePath;
                        DisplaySuccessMessage();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }

                }
                else
                {
                    DisplayErrorMessage();
                }
            }
            else
            {
                DisplayErrorMessage();
            }
        }
        private void ImportData(ExcelFile xclFile, string WorkSheet, out int successCount, out int totalCount)
        {
            successCount = 0;
            totalCount = 0;
            ExcelRowCollection rows = xclFile.Worksheets[WorkSheet].Rows;
            for (int i = 1; i < rows.Count; i++)
            {
                try
                {
                    ExcelRow row = rows[i];

                    // This is how to read each column value 
                    string firstColumn = Convert.ToString(row.Cells["A"].Value.ToString());
                    string secondColumn = Convert.ToString(row.Cells["B"].Value.ToString());
                    string thirdColumn = Convert.ToString(row.Cells["C"].Value.ToString());
                    // Write your Create Method Here
                    successCount++;
                }
                catch (Exception ex)
                {

                }
            }
            totalCount = rows.Count;
        }
        private void DisplaySuccessMessage()
        {
            divMessage.Style.Value = "display:block;";
            divMessage.Attributes.Add("class", "success marginTP");
            divMessage.InnerHtml = "Successfully Update data.";
        }
        private void DisplayErrorMessage()
        {
            divMessage.Style.Value = "display:block;";
            divMessage.Attributes.Add("class", "error marginTP");
            divMessage.InnerHtml = "Error Updating data.";
        }
    }
}